package com.stray.crawl.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.stray.crawl.BeanContext;
import com.stray.crawl.Constants.PlanKey;
import com.stray.crawl.model.CrawlPlan;
import com.stray.crawl.model.PlanUrl;

public class SQLitePlanRepository extends PlanRepository {

	private SimpleEntityManager em = BeanContext.get(SimpleEntityManager.class);
	private List<CrawlPlan> genericPlans;

	@Override
	public boolean buildSchema() {
		String[] sqls = new String[] {
			"DROP TABLE IF EXISTS \"crawl_plan\";			CREATE TABLE \"crawl_plan\" (\"id\" INTEGER PRIMARY KEY  NOT NULL ,\"name\" TEXT,\"group\" TEXT,\"category\" TEXT,\"method\" text,\"agent\" text,\"timeout\" integer,\"use_referer\" BOOL,\"use_cache\" BOOL,\"charset\" text,\"skip_first\" INTEGER,\"skip_last\" INTEGER,\"expression\" TEXT)",
			"DROP TABLE IF EXISTS \"field_expression\";		CREATE TABLE \"field_expression\" (\"id\" integer PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , \"crawl_plan_id\" integer NOT NULL , \"name\" text, \"expression\" text)",
			"DROP TABLE IF EXISTS \"url_pattern\";			CREATE TABLE \"url_pattern\" (\"id\" integer PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , \"crawl_plan_id\" integer NOT NULL , \"url\" text, \"convertable\" bool NOT NULL )",
			"DROP TABLE IF EXISTS \"url_param\";			CREATE TABLE \"url_param\" (\"id\" integer PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , \"url_pattern_id\" integer, \"name\" string, \"key\" string, \"type\" string, \"as_req_or_cnt\" char, \"optional\" bool NOT NULL )"
		};

		try {
			em.execute("BEGIN TRANSACTION;");
			for (String sql : sqls) {
				em.execute(sql);
			}
			em.execute("COMMIT;");

			return true;
		} catch (Exception e) {
			log.error("Cannot setup schema for plan storage. ", e);

			return false;
		}
	}

	@Override
	protected void savePlan(CrawlPlan plan) {
		try {
			em.clearIds(plan);
			em.persist(plan);
		} catch (Exception ex) {
			log.warn("Fail to save plan " + plan, ex);
		}
	}

	@Override
	protected Collection<CrawlPlan> getPlans() {
		List<CrawlPlan> plans = new ArrayList<CrawlPlan>();

		try {
			List<Integer> list = getGenericPlanIdList();

			for (Integer planId : list) {
				CrawlPlan plan = em.findLazyLoad(CrawlPlan.class, planId);
				plans.add(plan);
			}
		} catch (Exception ex) {
			log.warn("Fail to get all plans", ex);
			plans.clear();
		}

		return plans;
	}


	@Override
	protected List<CrawlPlan> getGenericPlans() {
		if (genericPlans == null) {
			genericPlans = new ArrayList<CrawlPlan>(0);

			try {
				List<Integer> list = getGenericPlanIdList();

				for (Integer planId : list) {
					CrawlPlan plan = em.findLazyLoad(CrawlPlan.class, planId);
					genericPlans.add(plan);
				}
			} catch (Exception ex) {
				log.warn("Fail to get generic plans", ex);
				genericPlans.clear();
			}
		}

		return genericPlans;
	}

	@Override
	protected Collection<CrawlPlan> getPlansByHost(String host) {
		List<CrawlPlan> plans = new ArrayList<CrawlPlan>();

		try {
			String sql = "SELECT DISTINCT p.id " +
						 "FROM 	 `crawl_plan` p " +
					 	 "JOIN 	 `url_pattern` u ON p.`id` = u.`crawl_plan_id` " +
						 "WHERE  u.`url` LIKE '%" + host + "/%' ";

			List<?> list = em.query(sql);
			Map<String, String> params = new HashMap<String, String>();

			for (Object planId : list) {
				params.put("id", String.valueOf(planId));

				List<CrawlPlan> planList = em.find(CrawlPlan.class, params, true);
				if (!planList.isEmpty()) {
					plans.add(planList.get(0));
				}
			}
		} catch (Exception ex) {
			log.warn("Fail to execute get plans. host: " + host, ex);
			plans.clear();
		}

		return plans;
	}

	@Override
	protected CrawlPlan getPlanByUrl(PlanUrl url) {
		CrawlPlan plan = null;

		try {
			String sql = "SELECT p.id " +
						 "FROM 	 `crawl_plan` p " +
						 "JOIN 	 `url_pattern` u ON p.`id` = u.`crawl_plan_id` " +
						 "WHERE	 u.`id` = " + url.getId();
			Number planId = (Number) em.querySingleResult(sql);

			plan = em.findLazyLoad(CrawlPlan.class, planId);
		} catch (Exception ex) {
			log.warn("Fail to execute get plans. url: " + url, ex);
		}

		return plan;
	}

	@SuppressWarnings("unchecked")
	private List<Integer> getGenericPlanIdList() throws SQLException {
		String sql = "SELECT DISTINCT p.id " +
				 	 "FROM 	 `crawl_plan` p " +
				 	 "WHERE  p.`" + PlanKey.PLAN_GROUP_COLUMN_NAME + "` = '" + PlanKey.PLAN_GROUP_GENERIC + "' ";

		return (List<Integer>) em.query(sql);
	}

}